
create database students
go

use students
go

create table stuInfo
(
	stuId int identity primary key,
	stuName varchar(10) not null ,
	stuAge char(2) not null,
	stuSex char(1) not null check(stuSex = '1' or stuSex = '0') ,
	time datetime
)
go

create table courseInfo
(
	courseId int identity primary key,
	courseName varchar(10) not null,
	courseMarks int not null
)
go

create table scoreInfo
(
	scoreId int identity primary key ,
	stuId int references stuInfo(stuId),
	courseId int references courseInfo(courseId),
	score int
)
go


insert into stuInfo(stuName,stuAge,stuSex,time)
select 'Tom','19','1','' union
select 'Jack','20','0','' union
select 'Rose','21','1','' union
select 'Lulu','19','1','' union
select 'Lili','21','0','' union
select 'abc','20','1','2007-01-07 01:11:36.590' 

insert into courseInfo(courseName,courseMarks)
select 'JavaBase','4' union
select 'HTML','2' union
select 'JavaScript','2' union
select 'SqlBase','2' 

insert into scoreInfo(stuId,courseId,score)
select '1','1','80' union
select '1','2','85' union
select '1','4','50' union
select '2','1','75' union
select '2','3','45' union
select '2','4','75' union
select '3','1','45' union
select '4','1','95' union
select '4','2','75' union
select '4','3','90' union
select '4','4','45' 




--1.查询出  每个学生  所选修的课程的数量和所选修的课程的考试的平均分

select si.stuName 学生,COUNT(sci.stuId) 选课数量,AVG(score) 考试平均分 from stuInfo si
	inner join scoreInfo sci on si.stuId = sci.stuId
		group by si.stuId,stuName

--2.查询出每门课程的选修的学生的个数和学生考试的总分

select courseName 课程名称,COUNT(si.courseId) 学生人数,SUM(score) 总分 from courseInfo ci
	inner join scoreInfo si on ci.courseId = si.courseId
		group by ci.courseId , courseName

--3.查询出性别一样并且年龄一样的学生的信息

select * from stuInfo A,stuInfo B 
	where a.stuAge = b.stuAge and a.stuSex = b.stuSex and a.stuId != b.stuId

--4.查询出学分一样的课程信息

select * from courseInfo ci where
	(
		select COUNT(*) from courseInfo where
		courseMarks = ci.courseMarks
	)>1

--5.查询出参加了考试的学生的学号，姓名，课程号和分数

select si.stuId ,stuName ,scoreId ,score from stuInfo si 
	inner join scoreInfo sci on sci.stuId = si.stuId
		group by si.stuId ,stuName ,scoreId ,score

--6.查询出参加了考试的学生的学号，课程号，课程名，课程学分和分数

select si.stuId 学号,ci.courseId 课程号,courseName 课程名,courseMarks 课程学分,score 分数 from scoreInfo sci
	inner join courseInfo ci on ci.courseId = sci.courseId
	inner join stuInfo si on si.stuId = sci.stuId

select * from stuInfo
select * from courseInfo
select * from scoreInfo

--7.查询出没有参加考试的学生的学号和姓名

 select * from  stuInfo si
	left join scoreInfo sci on si.stuId=sci.stuId 
		where score IS NULL

--8.查询出是周六周天来报到的学生

select * from stuInfo 
	where datepart(weekday,time)=1 or datepart(weekday,time)=7

--9.查询出姓名中有字母a的学生的信息

select * from stuInfo  where stuName like '%a%'

--10.查询出 选修了2门课程以上 的并且 考试平均分在70以上 的   学生的学号和考试平均分以及选修课程的数量

select si.stuId 学号,AVG(score) 考试平均分,COUNT(sci.stuId) 选课数量 from stuInfo si
	inner join scoreInfo sci on si.stuId = sci.stuId 
		group by si.stuId


